use std::collections::HashMap;
use std::vec;

use model_graph_types::database::DataSource;
use model_graph_types::datasource::{
    DeleteCondition, DeleteOptions, InsertOptions, UpdateCondition, UpdateOptions,
};

use model_graph_types::modeling::{Class, ClassPersistence, MetaConnector, Value};

use crate::connector::SimpleDataSourceConnector;
use crate::modeling_connector::SimpleMetaConnector;
use crate::query::{attributes_selects, attributes_wheres, relations_wheres};

pub fn genertate_query_one_sql(
    datasource: &DataSource,
    clazz: &Class,
    keys: &Vec<String>,
    mapping: ClassPersistence,
) -> anyhow::Result<String> {
    //
    //查询主键
    let key_names = &clazz.primary_key;
    let where_keys = key_names
        .split(",")
        .collect::<Vec<_>>()
        .iter()
        .map(|key_name| format!("{} = ? ", key_name))
        .collect::<Vec<String>>()
        .join(" and ");

    Ok(format!(
        "SELECT * FROM {} WHERE {}",
        mapping.table_name, where_keys
    ))
}

pub fn genertate_delete_by_keys_sql(
    datasource: &DataSource,
    clazz: &Class,
    keys: &Vec<String>,
    mapping: ClassPersistence,
) -> anyhow::Result<String> {
    //
    //查询主键
    let key_names = &clazz.primary_key;
    let where_keys = key_names
        .split(",")
        .collect::<Vec<_>>()
        .iter()
        .map(|key_name| format!("{} = ? ", key_name))
        .collect::<Vec<String>>()
        .join(" and ");

    Ok(format!(
        "DELETE FROM {} WHERE {}",
        mapping.table_name, where_keys
    ))
}

pub fn genertate_where_by_keys_sql(
    clazz: &Class,
    primary_key: &String,
    keys: &Vec<String>,
) -> anyhow::Result<String> {
    //
    //查询主键
    let where_keys = primary_key
        .split(",")
        .collect::<Vec<_>>()
        .iter()
        .map(|key_name| format!("{} = ? ", key_name))
        .collect::<Vec<String>>()
        .join(" and ");

    Ok(where_keys)
}

pub fn genertate_update_by_keys_sql(
    datasource: &DataSource,
    clazz: &Class,
    keys: &Vec<String>,
    key_names: &String,
    values: HashMap<String, Value>,
    mapping: ClassPersistence,
) -> anyhow::Result<(String, Vec<Value>)> {
    //
    //查询主键
    let where_keys = key_names
        .split(",")
        .collect::<Vec<_>>()
        .iter()
        .map(|key_name| format!("{} = ? ", key_name))
        .collect::<Vec<String>>()
        .join(" and ");

    let mut table_name = clazz.name.clone();
    table_name = mapping.table_name.clone();

    let mut sql = format!("");

    let mut args = vec![];

    let mut sets = vec![];

    //参数
    //获取所有的字段映射
    for (name, value) in &values {
        // TODO 取值， 获取到表名
        sets.push(format!(" {} = ? ", name.clone()));
        args.push(value.clone());
    }

    args.append(&mut keys.iter().map(|v| Value::String(v.clone())).collect());

    sql = format!(
        "update {} set {} where {} ",
        table_name,
        sets.join(","),
        where_keys,
    );
    Ok((sql, args))
}

pub async fn genertate_insert_data_sql(
    meta_datasource: &DataSource,
    class_identifier: &String,
    values: HashMap<String, Value>,
    options: Option<InsertOptions>,
) -> anyhow::Result<(String, Vec<Value>)> {
    let modeling = SimpleMetaConnector::new_with_meta_datasource(&Some(meta_datasource.clone()))?;
    let clazz = modeling.get_class(class_identifier).await?;
    // //获取映射
    let mapping = modeling.get_class_persistence(class_identifier).await?;

    let mut table_name = clazz.name.clone();
    table_name = mapping.table_name.clone();

    let mut args: Vec<Value> = vec![];

    let mut column_names: Vec<String> = vec![];
    let mut values_names: Vec<String> = vec![];

    tracing::debug!("{:?}", mapping);

    let ignores = if let Some(option) = &options {
        if let Some(ignores) = &option.ignores {
            ignores.clone()
        } else {
            vec![]
        }
    } else {
        vec![]
    };

    //获取所有的字段映射
    for column in mapping.columns {
        if ignores.contains(&column.column_name) {
            continue;
        }
        //取值
        if let Some(v) = values.get(&column.column_name) {
            column_names.push(column.column_name);
            values_names.push(String::from("?"));

            //
            args.push(v.clone());
        } else {
            //默认值
        }
    }

    let sql = format!(
        "INSERT INTO {}({})VALUES({})",
        table_name,
        column_names.join(","),
        values_names.join(",")
    );

    Ok((sql, args))
}

pub async fn genertate_delete_data_sql(
    ds_id: u32,
    meta_datasource: &DataSource,
    class_identifier: &String,
    conditions: DeleteCondition,
    options: Option<DeleteOptions>,
) -> anyhow::Result<(String, Vec<Value>)> {
    let modeling = SimpleMetaConnector::new_with_meta_datasource(&Some(meta_datasource.clone()))?;
    let ds_connector = SimpleDataSourceConnector::default();
    let clazz = modeling.get_class(class_identifier).await?;
    // //获取映射
    let mapping = modeling.get_class_persistence(class_identifier).await?;

    let mut table_name = clazz.name.clone();
    table_name = mapping.table_name.clone();

    let mut sql = format!("");

    let mut args = vec![];

    let mut wheres = vec![];

    if let Some(attributes) = &conditions.attributes {
        let (mut r, _, mut a) = attributes_wheres(
            &SimpleDataSourceConnector::default(),
            meta_datasource,
            &clazz,
            &mapping,
            attributes,
            None,
        )
        .await?;
        wheres.append(&mut r);
        args.append(&mut a);
    }

    //关系条件
    if let Some(relations) = &conditions.relations {
        let (mut r, _, mut a) = relations_wheres(
            &SimpleDataSourceConnector::default(),
            ds_id,
            meta_datasource,
            &clazz,
            &mapping,
            relations,
            None,
        )
        .await?;
        wheres.append(&mut r);
        args.append(&mut a);
    }

    if wheres.len() > 0 {
        sql = format!("delete from {} where {} ", table_name, wheres.join(" and "),);
    } else {
        //没有条件的删除
        // sql = format!("delete from {} ", table_name,);
        return Err(anyhow::anyhow!("没有条件的删除"));
    }

    Ok((sql, args))
}

pub async fn genertate_update_data_sql(
    ds_id: u32,
    meta_datasource: &DataSource,
    class_identifier: &String,
    conditions: UpdateCondition,
    values: HashMap<String, Value>,
    options: Option<UpdateOptions>,
) -> anyhow::Result<(String, Vec<Value>)> {
    let modeling = SimpleMetaConnector::new_with_meta_datasource(&Some(meta_datasource.clone()))?;
    let clazz = modeling.get_class(class_identifier).await?;
    // //获取映射
    let mapping = modeling.get_class_persistence(class_identifier).await?;

    let mut table_name = clazz.name.clone();
    table_name = mapping.table_name.clone();

    let mut sql = format!("");

    let mut args = vec![];

    let mut wheres = vec![];

    let mut sets = vec![];

    //更新字段
    //获取所有的字段映射
    for column in &mapping.columns {
        //取值
        if let Some(v) = values.get(&column.column_name) {
            sets.push(format!(" {} = ? ", column.column_name));
            //
            args.push(v.clone());
        } else {
            //默认值
        }
    }

    if let Some(attributes) = &conditions.attributes {
        let (mut r, _, mut a) = attributes_wheres(
            &SimpleDataSourceConnector::default(),
            meta_datasource,
            &clazz,
            &mapping,
            attributes,
            None,
        )
        .await?;
        wheres.append(&mut r);
        args.append(&mut a);
    }

    //关系条件
    if let Some(relations) = &conditions.relations {
        let (mut r, _, mut a) = relations_wheres(
            &SimpleDataSourceConnector::default(),
            ds_id,
            meta_datasource,
            &clazz,
            &mapping,
            relations,
            None,
        )
        .await?;
        wheres.append(&mut r);
        args.append(&mut a);
    }

    if wheres.len() > 0 {
        sql = format!(
            "update {} set {} where {} ",
            table_name,
            sets.join(","),
            wheres.join(" and "),
        );
    } else {
        //没有条件的删除
        // sql = format!("update from {} ", table_name,);
        return Err(anyhow::anyhow!("没有条件的更新"));
    }
    Ok((sql, args))
}
